package net.aaa.hotel.dao.impl;

import net.aaa.hotel.dao.RukuDao;
import net.aaa.hotel.entity.PageInfo;
import net.aaa.hotel.util.BaseDB;
import net.aaa.hotel.util.LayDate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Repository
public class RukuDaoImpl implements RukuDao {
    @Override
    public List<Map> query(PageInfo pagl) {
        String sql = "SELECT x.id,x.zprice,x.totalcount,y.`name` AS yname,x.jsr,x.date,x.`status` from t_ruku x,h_yg y WHERE x.rukuid=y.id order by x.id desc";
        if (pagl.getCondition()!=null && pagl.getCondition()!="") {
            sql+=pagl.getCondition();
        }
        sql+= " limit ?,?";
        int stat = (pagl.getCurr()-1)*pagl.getNums();
        return BaseDB.queryMap(sql,stat,pagl.getNums());
    }

    @Override
    public int count(PageInfo pagl) {
        String sql = "SELECT COUNT(*) a from t_ruku x,h_yg y WHERE x.rukuid=y.id ";
        if (pagl.getCondition()!=null && pagl.getCondition()!="") {
            sql+=pagl.getCondition();
        }
        return Integer.parseInt(BaseDB.queryMap(sql).get(0).get("a")+"");
    }

    @Override
    public List<Map> queryRuku(Integer id) {
        String sql = "SELECT rd.id id,se.ssName sname,tp.`name` pname,ru.id rid,se.ssGys gys,rd.rkcount count,rd.price price,se.ssId sid from t_rukudetail rd,t_ruku ru,t_sheshi se,t_ptype tp WHERE rd.rkid=ru.id AND rd.ssid=se.ssId AND se.ssTypeid=tp.id AND ru.id=?";

        return BaseDB.queryMap(sql,id);
    }

    @Override
    public List<Map> queryType() {
        String sql="select * from t_ptype";
        return BaseDB.queryMap(sql);
    }

    @Override
    public List<Map> querySs(Integer id) {
        String sql="select * from t_sheshi where ssTypeid=?;";
        return BaseDB.queryMap(sql,id);
    }

    @Override
    public List<Map> querySc(Integer id) {
        String sql="select * from t_sheshi where ssId=?;";
        return BaseDB.queryMap(sql,id);
    }

    @Override
    public int addRu(Map map) {
        String sql = "insert into t_ruku (date,rukuid,jsr,totalcount,zprice)values(now(),?,?,?,?)";
        return BaseDB.insertGetKey(sql,map.get("rukuid"),map.get("jsr"),map.get("totalcount"),map.get("zprice"));
    }

    @Override
    public int addRud(Map map1,int a) {
        System.out.println(a);
        String sql = "insert into t_rukudetail (ssid,rkcount,rkid,price)values(?,?,?,?)";
        return BaseDB.update(sql,map1.get("ssid"),map1.get("rkcount"),a,map1.get("ssPmoney"));
    }

    @Override
    public int addS(Map map1) {
        String sql = "update t_sheshi set ssCount=ssCount+? where ssId=?";
        return BaseDB.update(sql,map1.get("rkcount"),map1.get("ssid"));
    }

    @Override
    public Map<String, List> getRuKuMoney(LayDate layDate) {

        // 定义默认起始月
        int minMonth = 1;
        // 定义默认结束月
        int maxMonth = 12;
        // 拼接查询入库金额的sql
        String sql = "SELECT MONTH(date) month,SUM(totalcount) count FROM t_ruku tr WHERE 1=1 ";
        if (layDate.getYear()!=null&&layDate.getYear()!=""){
            sql+=" and year(date) = "+layDate.getYear();
        }else{
            sql+=" and year(date) = 2020";
        }
        if (layDate.getMonthMin()!=null&&layDate.getMonthMin()!=""){
            sql+=" and month(date) >="+layDate.getMonthMin();
            minMonth=Integer.parseInt(layDate.getMonthMin());
        }else {
            sql+=" and month(date) >= 1";
        }
        if (layDate.getMonthMax()!=null&&layDate.getMonthMax()!=""){
            sql+=" and month(date) <="+layDate.getMonthMax();
            maxMonth = Integer.parseInt(layDate.getMonthMax());
        }else {
            sql+=" and month(date) <= 12";
        }
        sql+=" group by month(date)";

        // 拼接查询出库金额的SQL
        String sql2 = "SELECT MONTH(date) month,SUM(num) count FROM t_chuku tr WHERE 1=1 ";
        if (layDate.getYear()!=null &&layDate.getYear()!=""){
            sql2+=" and year(date) = "+layDate.getYear();
        }else{
            sql2+=" and year(date) = 2020";
        }
        if (layDate.getMonthMin()!=null&&layDate.getMonthMin()!=""){
            sql2+=" and month(date) >="+layDate.getMonthMin();
        }else {
            sql2+=" and month(date) >= 1";
        }
        if (layDate.getMonthMax()!=null&&layDate.getMonthMax()!=""){
            sql2+=" and month(date) <="+layDate.getMonthMax();
        }else {
            sql2+=" and month(date) <= 12";
        }
        sql2+=" group by month(date)";
        // 查询入库金额的结果
        List<Map> list = BaseDB.queryMap(sql);

        // 查询出库金额的结果
        List<Map> list2 = BaseDB.queryMap(sql2);

        // 定义一个Map 装载 处理后的数据，页面要的数据类型
        Map<String, List> mapList = new HashMap<>(64);

        // 装填月份的list
        List<Object> monthData = new ArrayList<>();

        // 装填入库金额的list
        List<Double> ruKuCount = new ArrayList<>();

        // 装填出库金额的list
        List<Double> ckCount = new ArrayList<>();

        //月份的循环
        for (int i = minMonth; i <= maxMonth; i++) {
            double value = 0;
            double value2 = 0;
            monthData.add(i+"");
            for (Map map : list) {
                // 如果 查到的map中有相同的月份，则将对应的钱数存到ruKuMoney对应的月份的位置
                if (Integer.parseInt(map.get("month")+"")==i){
                    value =Double.parseDouble(map.get("count")+"") ;
                }
            }
            for (Map map : list2) {
                // 如果 查到的map中有相同的月份，则将对应的钱数存到ckMoney对应的月份的位置
                if (Integer.parseInt(map.get("month")+"")==i){
                    value2 =Double.parseDouble(map.get("count")+"") ;
                }
            }
            // 如果 查到的map中没有相同的月份，0 对应的月份的位置
            ruKuCount.add(value);
            ckCount.add(value2);
        }
        mapList.put("ruKuCount",ruKuCount);
        mapList.put("ckCount",ckCount);
        mapList.put("monthData",monthData);

        return mapList;
    }

    @Override
    public List<Map> getAllTypeAndCount() {
        String sql = "SELECT p.name name,SUM(s.ssCount) value FROM  t_ptype p,t_sheshi s where p.id=s.ssTypeid GROUP BY s.ssTypeid";
        return BaseDB.queryMap(sql);
    }

    @Override
    public int queryDingdDan() {
        String sql = "SELECT * FROM t_ruku WHERE STATUS=0";
        if (BaseDB.queryMap(sql).size()>0) {
           return 1;
        }
        return 0;
    }

    @Override
    public List<Map> queryRuKuDatil(Integer rkid) {
        String sql ="SELECT * FROM t_rukudetail WHERE rkid=?";
        return BaseDB.queryMap(sql,rkid);
    }

    @Override
    public int upda(Integer rkid) {
        String sql = "update t_ruku set status=1 where id=?";
        return BaseDB.update(sql,rkid);
    }
}
